Re: ISOLATION LEVEL SERIALIZABLE - Mailing list pgsql-general

>In table two you would not create a serial instead you would create an
>INTEGER because serial is a counter and the values in table 2 may not be
>in table 1.
>
>Use a transaction like as follows
>
>BEGIN;
>INSERT INTO TABLE1 VALUES (Whatever values);
>var = SELECT CURRVAL('sequence_name');
>INSERT INTO TABLE2 VALUES (var,whatever else);
>COMMIT;
>

But this is the race condition I am trying to avoid. Someone can
insert before I get the currval and it will beincremented and this
will result in invalid data. Right now, I'm doing exactly that but I
add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
PG Explorer!

>So you would start the transaction then insert into the first table.
>You would then get the current value of the sequence that the first table
>created.
>You would then insert this value into table2 along with anything else
>
>HTH
>Darren Ferguson
>
>On Tue, 26 Mar 2002, power2themacs wrote:
>
>>  I am using 7.2 and JDBC. I have a very simple situation where I
>>  insert an item with a primary key which is a SERIAL. In the same
>>  transaction I need to insert a reference in a separate table to this
>>  item. Of course, race conditions could occur if I didn't use this
>>  special isolation level. But is there no way in which I could use
>>  row-level locking instead? Certainly, I am not updating the table I
>>  just inserted to, so the FOR UPDATE would never unlock. Is the
>>  isolation level my only option? I noticed that psql displays the oid
>>  after an INSERT. That would be exactly what I need but JDBC doesn't
>>  seem to offer this.
>>
>>  (Right now I set the isolation level and just get the SERIAL's
>>current value.)
>>
>>  |---table1----------------|   |---table2-----------------|
>>  | id SERIAL PRIMARY KEY    |  | id SERIAL REFERNCES table1 |
>>
>>
>>  Short version:
>>  I just inserted into table1 and need these in the same transaction.
>>  How can I get the id I just inserted into table2? Thanks folks.
>>  --
>>  ><><><><><><><><><><><><
>>  power2themacs@yahoo.com
>>
>>  _________________________________________________________
>>  Do You Yahoo!?
>>  Get your free @yahoo.com address at http://mail.yahoo.com
>>
>>
>>  ---------------------------(end of broadcast)---------------------------
>  > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  >


--
><><><><><><><><><><><><
AgentM
agentm@cmu.edu

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com